<?php

/**
 *
 * Class MyDB
 *
 * A simple, yet very efficient class for managing common MySQL interactions
 * with PHP.
 *
 * This is a work in progress and I'll be adding to it.
 *
 * Questions?
 *
 *      Email me : rogere84 ( at ) gmail ( dot ) com
 *
 *  SQL Script for examples:
 *
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `name`, `position`) VALUES
(1, 'Joe Bloggs', 'Owner'),
(2, 'Jane Bloggs', 'Wife');
 *
 *
 *
 * @author Roger E Thomas - September 2011
 * @website http://www.rogerethomas.com
 * @copyright 2011 Roger E Thomas
 *
 *
 */
class MyDB {
    private $_Con;
    const MYSQL_HOST = '127.0.0.1'; // Database Host
    const MYSQL_USER = 'root'; // Database User
    const MYSQL_PASS = 'concentrador'; // Database Password
    const MYSQL_NAME = 'hostelv1'; // Database Name

    function __construct() {

        $this->_Con = mysql_connect(self::MYSQL_HOST,self::MYSQL_USER,self::MYSQL_PASS);

        if (!$this->_Con) {
            exit('Connection failed to MySQL host <b>' . self::MYSQL_HOST . '</b>');
        }

        mysql_select_db(self::MYSQL_NAME,$this->_Con);

        if (mysql_error()) {
            exit('Connection to <b>' . self::MYSQL_NAME . '</b> failed.');
        }

    }
    
     public function customSelectFrom($SQL) {

        

        $exec = mysql_query($SQL,$this->_Con);

        $num = mysql_num_rows($exec);
        if ($num != 0) {
            $result = array();
            while (NULL != ($row = mysql_fetch_assoc($exec))) {
                $thisset = array();
                foreach ($row as $key => $val) {
                        $thisset[$key] = $val;
                }
                $result[] = $thisset;
            }
        }else{
            $result[]=array();
        }

        $returnarray = array('sql' => $SQL, 'num' => $num, 'result' => $result);

        return $returnarray;

    }

    /**
     *
     * selectFrom
     *
     * Simple method to select a result set from a mysql database
     *
     * @param str $table
     * @param str / array $columns
     * @param array $where
     * @param boolean $like
     * @param str $orderby
     * @param str $direction
     * @param int $limit
     * @param int $offset
     * @return array('sql' => $SQL, 'num' => $num, 'result' => $result);
     *
     *
     *
     *
     * Example Usage 1 :
     *
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con -> selectFrom("users", $columns = null, $where = null, $like = false, $orderby = "id", $direction = "DESC", $limit = null, $offset = null);
     * </code>
     *
     * Generated SQL:
     * SELECT * FROM users ORDER BY id DESC
     *
     * Return Example:
     *
        Array
        (
            [sql] => SELECT * FROM users ORDER BY id DESC
            [num] => 2
            [result] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [name] => Jane Bloggs
                            [position] => Wife
                        )

                    [1] => Array
                        (
                            [id] => 1
                            [name] => Joe Bloggs
                            [position] => Owner
                        )

                )

        )
     *
     * Example Usage 2 :
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con->selectFrom($table = "people", $columns = array('id','name','position'), $where = array('name'=>'Joe Bloggs'), $like = false, $orderby = "id", $direction = "DESC", $limit = 2, $offset = 1);
     * </code>
     *
     * This would run the following SQL statement:
     * SELECT id, name, position FROM users WHERE name='Joe Bloggs' ORDER BY id DESC LIMIT 1, 2
     *
     *
     *
     * Example Usage 3 :
     *
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con->selectFrom($table = "users", $columns = array('id','name','position'), $where = array('name'=>'Joe'), $like = true, $orderby = "id", $direction = "DESC", $limit = 2, $offset = 1);
     * </code>
     *
     * This would run the following SQL statement:
     *
     * SELECT id, name, position FROM users WHERE name LIKE '%Joe%' ORDER BY id DESC LIMIT 1, 2
     *
     *
     *
     * Return Value:
     * array(
     *         sql => The actual SQL statement that this file ran against the database,
     *         num => The number of results fetched from the database,
     *         result => each row set in its own array
     * )
     *
     */
    public function selectFrom($table, $columns = null, $where = null, $like = false, $orderby = null, $direction = null, $limit = null, $offset = null) {

        $SQL = "SELECT ";
        if ($columns != null) {
            if(is_array($columns)) { $cols = implode(", ",$columns); }
            else { $cols = $columns; }
            $SQL.= $cols." ";
        }
        else {
            $SQL.= "* ";
        }
        $SQL.= "FROM ".$table;

        if ($where != null) {
            $SQL.=" WHERE";
            if ($like == true) {
                $whe = $this->_helperWhereLikeGenerate($where);
            }
            else {
                $whe = $this->_helperWhereEqualsGenerate($where);
            }
            $SQL.= $whe;
        }
        if ($direction != null && $orderby != null) {
            if (strtolower($direction) == "asc" || strtolower($direction) == "desc") {
                $order = " ORDER BY ".$orderby." ".$direction;
                $SQL .= $order;
            }
        }
        if ($limit != null) {
            if ($offset == null) {
                $lim = " LIMIT ".$limit;
                $SQL .= $lim;
            }
            else {
                $lim = " LIMIT ".$offset.", ".$limit;
                $SQL .= $lim;
            }
        }

        $exec = mysql_query($SQL,$this->_Con);

        $num = mysql_num_rows($exec);
        if ($num != 0) {
            $result = array();
            while (NULL != ($row = mysql_fetch_assoc($exec))) {
                $thisset = array();
                foreach ($row as $key => $val) {
                        $thisset[$key] = $val;
                }
                $result[] = $thisset;
            }
        }

        $returnarray = array('sql' => $SQL, 'num' => $num, 'result' => $result);

        return $returnarray;

    }

    /**
     * insertInto
     *
     * Simple method to insert a row of data into a mysql
     * database. This function relies on a mysql_insert_id()
     * to be returned so you must have an auto increment
     * field in the table.
     *
     * @param str $table
     * @param array $fields
     *
     * Example Usage 1 :
     *
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con -> insertInto("users", $fields = array("id" => "", "name" => "John Doe", "position" => "Unknown"));
     * </code>
     *
     * Generated SQL:
     * INSERT INTO users (id, name, position) VALUES ('', 'John Doe', 'Unknown')
     *
     * Note that we place id into the array put into the
     * function but leave its value as empty. This is our
     * auto-increment columns which we rely on to identify
     * a successful insertion.
     *
     * Return Example: (Success)
     *
        Array
        (
            [status] => success
            [id] => 4
        )
     *
     *
     * Return Example: (Failure)
     *
        Array
        (
            [status] => failure
            [id] => 0
        )
     *
     *
     */
    public function insertInto($table = null, $fields) {

        $cleanfields = $this->_helperCleanFields($fields);

        $inserts = $this->_helperExtractFieldsValues($cleanfields);

        $SQL = "INSERT INTO ";
        $SQL.= $table . " ";
        $SQL.= $inserts['fields'] . " ";
        $SQL.= "VALUES ";
        $SQL.= $inserts['values'] . " ";

        mysql_query($SQL, $this->_Con);

        $id = mysql_insert_id($this->_Con);

        if (isset($id) && is_numeric($id) && $id != 0) {
            $status = "success";
        }
        else {
            $status = "failure";
        }

        $insertid = $id;

        $return = array('status' => $status, 'id' => $insertid, 'sql' => $SQL);

        return $return;

    }

    /**
     * updateTable
     *
     * Simple method to update a MySQL table giving specific reference
     * to values to set, and under what conditions (WHERE).
     *
     * Also supports %LIKE% when $like = true
     *
     * @param str $table
     * @param array $fields
     * @param array $where
     * @param boolean $like
     *
     *
     * Example Usage 1 :
     *
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con -> updateTable("users", $fields = array("name" => "a","position" => "b"), $where = array("id"=>"2"), $like = true);
     * </code>
     *
     * Generated SQL:
     * UPDATE users SET name='a', position='b' WHERE id LIKE '%2%'
     *
     * Return Example: (Success)
     *
        Array
        (
            [sql] => UPDATE users SET name='a', position='b' WHERE id LIKE '%2%'
            [status] => success
            [affected] => 1
        )
     *
     *
     * Return Example: (Failure)
     *
        Array
        (
            [sql] => UPDATE users SET nosuchfield='a', position='b' WHERE id LIKE '%2%'
            [status] => failure
            [affected] => 0
        )
     *
     *
     */
    public function updateTable($table, $fields, $where = null, $like = false) {

        $cleanfields = $this->_helperCleanFields($fields);

        if ($where != null) {
            $wherebegin = " WHERE";
            if ($like == false) {
                $wherecommand = $this->_helperWhereEqualsGenerate($where);
            }
            else {
                $wherecommand = $this->_helperWhereLikeGenerate($where);
            }
            $wherecommand = $wherebegin.$wherecommand;
        }
        else {
            $wherecommand = "";
        }


        $updates = $this->_helperExtractUpdateValues($cleanfields);

        $SQL = "UPDATE ";
        $SQL.= $table . " ";
        $SQL.= $updates . "";
        $SQL.= $wherecommand . " ";

        mysql_query($SQL, $this->_Con);

        $num = mysql_affected_rows($this->_Con);

        if (isset($num) && is_numeric($num) && $num != 0 && $num != -1) {
            $status = "success";
        }
        else {
            $status = "failure";
        }

        $affected = $num;

        $return = array('sql' => $SQL, 'status' => $status, 'affected' => $affected);

        return $return;

    }


    
    
     public function customExec($SQL) {

        mysql_query($SQL, $this->_Con);

        $num = mysql_affected_rows($this->_Con);

        if (isset($num) && is_numeric($num) && $num != 0 && $num != -1) {
            $status = "success";
        }
        else {
            $status = "failure";
        }

        $affected = $num;

        $return = array('sql' => $SQL, 'status' => $status, 'affected' => $affected);

        return $return;

    }
    
    
    /**
     * deleteFrom
     *
     * A simple method to delete data from a mysql database.
     * This requires no real input other than a table name,
     * HOWEVER... If you don't specify the $where array or the
     * $limit then you will get some nasty surprises. I would
     * strongly suggest you run this against a dummy database
     * before testing on a production system.
     *
     * @param str $table
     * @param array $where
     * @param boolean $like
     * @param int $limit
     *
     * Example Usage 1 : (Success)
     *
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con -> deleteFrom("users", $where = array('name'=>'John Doe','position'=>'Unknown'), $like = false, $limit = 1);
     * </code>
     *
     * Generated SQL:
     * DELETE FROM users WHERE name='John Doe' AND position='Unknown' LIMIT 1
     *
     * Return Example: (Success)
     *
        Array
        (
            [sql] => DELETE FROM users WHERE name='John Doe' AND position='Unknown' LIMIT 1
            [status] => success
            [affected] => 1
        )
     *
     * Example Usage 2 : (Success)
     *
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con -> deleteFrom("users", $where = array('name'=>'John','position'=>'Unknown'), $like = true, $limit = 1);
     * </code>
     *
     * Generated SQL:
     * DELETE FROM users WHERE name LIKE '%John%' AND position LIKE '%Unknown%' LIMIT 1
     *
     * Return Example: (Success)

        Array
        (
            [sql] => DELETE FROM users WHERE name LIKE '%John%' AND position LIKE '%Unknown%' LIMIT 1
            [status] => success
            [affected] => 1
        )
     *
     *
     * Example Usage 3 : (Failure)
     *
     * <code>
     * require 'MyDB.php';
     * $con = new MyDB();
     * $query = $con -> deleteFrom("users", $where = array('nosuchfield'=>'John','position'=>'Unknown'), $like = false, $limit = 1);
     * </code>
     *
     * Generated SQL:
     * DELETE FROM users WHERE name LIKE '%John%' AND position LIKE '%Unknown%' LIMIT 1
     *
     * Return Example: (Failure)

        Array
        (
            [sql] => DELETE FROM users WHERE nosuchfield='John' AND position='Unknown' LIMIT 2
            [status] => failure
            [affected] => -1
        )
     *
     */
    public function deleteFrom($table, $where = null, $like = false, $limit = 1) {

        $SQL = "DELETE ";
        $SQL.= "FROM ".$table;

        if ($where != null) {
            $SQL.=" WHERE";
            if ($like == true) {
                $whe = $this->_helperWhereLikeGenerate($where);
            }
            else {
                $whe = $this->_helperWhereEqualsGenerate($where);
            }
            $SQL.= $whe;
        }

        if ($limit != null) {
            $lim = " LIMIT ".$limit;
            $SQL .= $lim;
        }

        mysql_query($SQL,$this->_Con);

        $num = mysql_affected_rows($this->_Con);

        if (isset($num) && is_numeric($num) && $num != 0 && $num != -1) {
            $status = "success";
        }
        else {
            $status = "failure";
        }

        $affected = $num;

        $return = array('sql' => $SQL, 'status' => $status, 'affected' => $affected);

        return $return;

    }


















    /**
     * A helper function to generate x LIKE %y% AND y LIKE %z%
     * @param array $where
     * @return str
     */
    private function _helperWhereLikeGenerate($where) {

        if (!empty($where)) {
            $whe = "";
            $i = 0;
            foreach ($where as $wKey => $wVal) {
                $i++;
                if ($i != 1) { $wheAdd = " AND "; }
                else { $wheAdd = " "; }
                $whe .= $wheAdd.$wKey . " LIKE '%".mysql_real_escape_string($wVal,$this->_Con)."%'";
            }
        }
        else {
            $whe = "";
        }

        return $whe;

    }

    /**
     * A helper function to generate x = y AND y = z
     * @param array $where
     * @return str
     */
    private function _helperWhereEqualsGenerate($where) {

        if (!empty($where)) {
            $whe = "";
            $i = 0;
            foreach ($where as $wKey => $wVal) {
                $i++;
                if ($i != 1) { $wheAdd = " AND "; }
                else { $wheAdd = " "; }
                $whe .= $wheAdd.$wKey . "='".mysql_real_escape_string($wVal,$this->_Con)."'";
            }
        }
        else {
            $whe = "";
        }

        return $whe;

    }

    /**
     * A helper function to cleanse values
     * @param array $fields
     * @return array clean key => values
     */
    private function _helperCleanFields($fields) {

        $cleanfields = array();

        foreach ($fields as $fkey => $fval) {

            $cleanfields[$fkey] = mysql_real_escape_string($fval,$this->_Con);

        }

        return $cleanfields;

    }

    /**
     * A helper function to extract field names
     * and values from an array of clean variables.
     * @param array $cleanfields
     * @return array ( fields, values )
     */
    private function _helperExtractFieldsValues($cleanfields) {

        $return = array();

        if (!empty($cleanfields)) {
            $keys = array_keys($cleanfields);
            $vals = array_values($cleanfields);

            $insertFields = implode(", ",$keys);
            $insertFields = "(".$insertFields.")";
            $insertValues = implode("', '",$vals);
            $insertValues = "('".$insertValues."')";

            $return['fields'] = $insertFields;
            $return['values'] = $insertValues;
        }

        return $return;

    }

    /**
     * A helper function to extract the cleansed fields
     * into a string for the UPDATE command
     * @param array $cleanfields
     */
    private function _helperExtractUpdateValues($cleanfields) {

        $return = "";

        if (!empty($cleanfields)) {
            $return .= "SET";
            $i = 0;
            foreach ($cleanfields as $key => $value) {
                $i++;
                if ($i != 1){ $begin = ","; }
                else { $begin = ""; }
                $return .= $begin." " . $key . "='".$value."'";

            }
        }

        return $return;

    }

}

?>